# 1.查询student表的所有记录
# select * from student;
# 2.查询student表的第2条到4条记录
# select * from student limit1,3
# 3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
# select id,name,department from student;
# 4.从student表中查询计算机系和英语系的学生的信息
# select * from student where department='计算机系' or department='英语系';
# 5.从student表中查询年龄18~22岁的学生信息
# select * from student where 25<=(2021-birth) and (2021-birth)<=30; between 18 and 22;
# 6.从student表中查询每个院系有多少人 
# select department,count(*) from student group by department;
# 7.从score表中查询每个科目的最高分
# select max(grade) from score group by c_name;
# 8.查询李四的考试科目（c_name）和考试成绩（grade）
# select c_name,grade from score where stu_id=(select id from student where name='李四');
# 9.用连接的方式查询所有学生的信息和考试信息
# select * from student LEFT JOIN score on student.id=score.stu_id;
# 10.计算每个学生的总成绩
# select name,sum(grade) from student left join score on student.id=score.stu_id group by name;
# 11.计算每个考试科目的平均成绩
# select c_name,avg(grade) from score group by c_name;
# 12.查询计算机成绩低于95的学生信息
# select * from student left join score on student.id=score.stu_id where c_name='计算机' and score<95;
#
# 13.将计算机考试成绩按从高到低进行排序
# select * from score where c_name='计算机' order by grade desc;
# 14.查询姓张或者姓王的同学的姓名
# select * from student where name like '张%' or name like '王%';
#
# 15.查询有哪些学生id找不到对应的成绩
# select * from student where id not in (select stu_id from score);



# 1.请用python函数实现斐波那契数列（1，1，2，3，5，8，13……），要求函数可根据传入的实参n来生成对应的长度为n的数列。
# def fib(n):
#     fib = []
#     for i in range(n):
#         if len(fib) < 2:
#             fib.append(1)
#             continue
#         fib.append(fib[-1]+fib[-2])
#     return fib
#
# print(fib(5))

# 2.判断用户输入的是用户名是否已注册？ （数据来自mysql数据的woniusales.user表）
# import pymysql
#
# def select():
#     cnn=pymysql.connect(host='192.168.13.159',port=3306,user='root',password='123456',charset='utf8',db='woniusales')
#     cur=cnn.cursor(pymysql.cursors.DictCursor)
#     sql = "select * from woniusales.user"
#     cur.execute(sql)
#     return cur.fetchall()
#     cur.close()
#     cnn.close()
#
# userlist=select()
# username=input('请输入用户名：')
# for i in userlist:
#     if i['username']==username:
#         print('用户名已注册。')
#         break
# else:
#     print('未注册')

# 3.把woniusales.user表数据读出来，然后存在csv文件中
import pymysql,csv

def select():
    cnn=pymysql.connect(host='192.168.13.159',port=3306,user='root',password='123456',charset='utf8',db='woniusales')
    cur=cnn.cursor(pymysql.cursors.DictCursor)
    sql = "select * from user"
    cur.execute(sql)
    return cur.fetchall()
    cur.close()
    cnn.close()

user=select()
file_write=open("user.csv",mode="w",encoding="utf8",newline="")
csv_dict_write=csv.DictWriter(file_write,delimiter='|',fieldnames=["userid","username","password","realname","phone","role","createtime"])
csv_dict_write.writerows(user)
file_write.close()





